############################################################################################
#                        Fonctions de gestion des membres du Bureau                        #
############################################################################################

# Requête retournant la liste des membres du bureau pour une date donnée
sub searchBureauMembers { #(nom,prenom,promotion,filiere,login,date)
  my @quotedArgs;
  my $i=0;
  foreach my $arg (@_) {
    $quotedArgs[$i++]=quotemeta($arg);
  }

  my $date = convertDate($_[5]) ;

  my $req = <<"SQL";
  SELECT idBureau, nomPersonne, prenomPersonne, adherent.login, filiere, promotion, dateEntree, dateSortie from adherent, eleve, personne, bureau
      WHERE bureau.idAdherent = adherent.idAdherent
        AND adherent.login = eleve.login
        AND eleve.idPersonne = personne.idPersonne
        AND personne.nomPersonne like '%$quotedArgs[0]%'
        AND personne.prenomPersonne like '%$quotedArgs[1]%'
        AND eleve.promotion like '%$quotedArgs[2]%'
        AND eleve.filiere like '%$quotedArgs[3]%'
        AND eleve.login like '%$quotedArgs[4]%'
        AND ('$date' = '' OR '$date' BETWEEN dateEntree AND dateSortie)
        ORDER BY dateSortie, nomPersonne, prenomPersonne;
SQL
  my $hashMembers = $dbd->selectall_hashref($req, 'idBureau');

    return $hashMembers;
}

# Requête retournant les information concernant un membre du bureau
sub quickSearchBureauMember {
  my $req = <<"SQL";
  SELECT dateEntree, dateSortie FROM bureau
  WHERE idBureau='$_[0]';
SQL
  return $dbd->selectrow_array($req);
}

# Requête retournant VRAI si l'adherent est membre du bureau
sub isInBureau {
my $req = <<"SQL";
  SELECT * FROM bureau
  WHERE idAdherent='$_[0]'
  AND CURDATE() < dateSortie;
SQL
return $dbd->selectrow_array($req) != ();
}

# Requête ajoutant un membre du bureau
sub addBureauMember {#(nom,prenom,promotion,filiere,login,dateAdhesion, dateEntree)
  my @quotedArgs;
  my $i=0;
  foreach my $arg (@_) {
    $quotedArgs[$i++]=$dbd->quote($arg);
  }
  
  my $subscriber = searchMember($_[0],$_[1],$_[2],$_[3],$_[4], convertDate($_[5]));
  my @idSubscriber = values(%$subscriber);
  my $idSubscriber = $idSubscriber[0]->{idAdherent};

  my $req = $dbd->prepare("INSERT INTO bureau (idAdherent, dateEntree) VALUE ($idSubscriber, str_to_date('$_[6]','%d/%m/%Y'));");
  $req->execute;
  $req->finish;
}

# Requête supprimant un membre du bureau
sub deleteBureauMember { #(idBureau)
  my $idBureau = $dbd->quote($_[0]);
  my $req = <<"SQL";
  DELETE FROM bureau
    USING bureau
    WHERE bureau.idBureau = $idBureau;
SQL
my $prep = $dbd->prepare($req);
$prep->execute;
$prep->finish;
}

# Requête permettant de mettre à jour les informations concernant un membre du bureau
sub updateBureauMember { #(idBureau, dateEntree, dateSortie)
  my @quotedArgs;
  my $i=0;
  foreach my $arg (@_) {
    $quotedArgs[$i++]=quotemeta($arg);
  }
    my $dateEntree = convertDate($_[1]);
    my $dateSortie = convertDate($_[2]);

  my $req = <<"SQL";
  UPDATE bureau
      SET dateEntree = '$dateEntree',
          dateSortie = '$dateSortie'
      WHERE idBureau = '$quotedArgs[0]'
SQL
  my $prep = $dbd->prepare($req);
  $prep->execute;
  $prep->finish;
}

1;
